<?php defined('SYSPATH') or die('No direct script access.');

class Model_Finance_Overview extends ORM
{
    public static function income_statistics($params)
    {
        $model = DB::select(DB::expr("sline_a.ordersn,sline_b.total_price-sline_b.privileg_price as pay_price"))->from(['member_order','a']);
        $model->join(['member_order_compute','b'], 'LEFT');
        $model->on('a.id', '=', DB::expr("sline_b.order_id"));
        $model->where('a.status','in',[2,5,4]);
        $model->and_where('a.addtime', '>=', $params['start_date']);
        $model->and_where('a.addtime', '<=', $params['end_date']);
        $model->and_where('a.pid', '=', 0);

        $total_list  = $model->execute()->as_array();
        $total_price = number_format(array_sum(array_column($total_list, 'pay_price')), 2);
        $total_num   = count($total_list);

        $brokerage_model  = clone $model;
        $brokerage_list   = $brokerage_model->and_where('a.status', 'in', [2,4])->execute()->as_array();
        $brokerage        = number_format(array_sum(array_column($brokerage_list, 'pay_price')), 2);
        $brokerage_num    = count($brokerage_list);
        $total_refund_fee = 0;
        foreach ($brokerage_list as $b) {
            $refund_fee       = DB::select(DB::expr("sum(refund_fee) as refund_fee"))->from('member_order_refund')
                ->where('ordersn', '=', $b['ordersn'])
                ->and_where('status', '=', 1)
                ->execute()->get('refund_fee');
            if($refund_fee>0){
                $total_refund_fee = bcadd($total_refund_fee, $refund_fee, 2);
            }
        }
        $brokerage   = str_replace(',', '', $brokerage);
        $total_price = str_replace(',', '', $total_price);
        $brokerage   = bcsub($brokerage, $total_refund_fee, 2);
        $total_price = bcsub($total_price, $total_refund_fee, 2);

        $finish_brokerage_model = clone $model;
        $finish_brokerage_list  = $finish_brokerage_model->and_where('a.status', '=', 5)->execute()->as_array();
        $finish_brokerage       = number_format(array_sum(array_column($finish_brokerage_list, 'pay_price')), 2);
        $finish_brokerage_num   = count($finish_brokerage_list);
        $total_refund_fee       = 0;
        foreach ($finish_brokerage_list as $f) {
            $refund_fee = DB::select(DB::expr("sum(refund_fee) as refund_fee"))->from('member_order_refund')
                ->where('ordersn', '=', $f['ordersn'])
                ->and_where('status', '=', 1)
                ->execute()->get('refund_fee');
            if ($refund_fee > 0) {
                $total_refund_fee = bcadd($total_refund_fee, $refund_fee, 2);
            }
        }
        $finish_brokerage = str_replace(',', '', $finish_brokerage);
        $finish_brokerage = bcsub($finish_brokerage, $total_refund_fee, 2);
        $total_price      = bcsub($total_price, $total_refund_fee, 2);


        return [
            'total_price'          => number_format($total_price, 2, '.', ','),
            'total_num'            => $total_num,
            'brokerage'            => number_format($brokerage, 2, '.', ','),
            'brokerage_num'        => $brokerage_num,
            'finish_brokerage'     => number_format($finish_brokerage, 2, '.', ','),
            'finish_brokerage_num' => $finish_brokerage_num
        ];
    }

    public static function expenditure_statistics($params)
    {
        $model = DB::select(DB::expr("sline_a.*"))->from([
            'member_withdraw',
            'a'
        ])
            ->join([
                'member',
                'b'
            ], 'LEFT')->on('a.memberid', '=', 'b.mid')
            ->where('a.addtime', '>=', $params['start_date'])
            ->and_where('a.addtime', '<=', $params['end_date']);

        $total_model = clone $model;
        $total_list  = $total_model->and_where('status', 'IN', [
            0,
            1
        ])->execute()->as_array();
        $total_price = number_format(array_sum(array_column($total_list, 'withdrawamount')), 2);
        $total_num   = count($total_list);

        $brokerage_model = clone $model;
        $brokerage_list  = $brokerage_model->and_where('a.status', '=', 0)->execute()->as_array();
        $brokerage       = number_format(array_sum(array_column($brokerage_list, 'withdrawamount')), 2);
        $brokerage_num   = count($brokerage_list);

        $finish_brokerage_model = clone $model;
        $finish_brokerage_list  = $finish_brokerage_model->and_where('a.status', '=', 1)->execute()->as_array();
        $finish_brokerage       = number_format(array_sum(array_column($finish_brokerage_list, 'withdrawamount')), 2);
        $finish_brokerage_num   = count($finish_brokerage_list);

        return [
            'total_price'          => $total_price,
            'total_num'            => $total_num,
            'brokerage'            => $brokerage,
            'brokerage_num'        => $brokerage_num,
            'finish_brokerage'     => $finish_brokerage,
            'finish_brokerage_num' => $finish_brokerage_num
        ];
    }

    public static function supplier_statistics($params)
    {
        $model = DB::select()->from('supplier_brokerage')
            ->where('addtime', '>=', $params['start_date'])
            ->and_where('addtime', '<=', $params['end_date']);

        $total_list  = $model->execute()->as_array();
        $total_price = number_format(array_sum(array_column($total_list, 'brokerage')), 2);
        $total_num   = count($total_list);

        $brokerage_model = clone $model;
        $brokerage_list  = $brokerage_model->and_where('status', '=', 1)->execute()->as_array();
        $brokerage       = number_format(array_sum(array_column($brokerage_list, 'brokerage')), 2);
        $brokerage_num   = count($brokerage_list);

        $finish_brokerage_model = clone $model;
        $finish_brokerage_list  = $finish_brokerage_model->and_where('status', '=', 2)->execute()->as_array();
        $finish_brokerage       = number_format(array_sum(array_column($finish_brokerage_list, 'brokerage')), 2);
        $finish_brokerage_num   = count($finish_brokerage_list);

        return array(
            'total_price'          => $total_price,
            'total_num'            => $total_num,
            'brokerage'            => $brokerage,
            'brokerage_num'        => $brokerage_num,
            'finish_brokerage'     => $finish_brokerage,
            'finish_brokerage_num' => $finish_brokerage_num
        );
    }

    public static function fenxiao_statistics($params)
    {
        if (St_Functions::is_normal_app_install('mobiledistribution')) {
            $model = DB::select('a.order_id', 'd.amount')->from([
                'fenxiao_data',
                'a'
            ])
                ->join([
                    'member_order',
                    'b'
                ], 'LEFT')
                ->on('a.order_id', '=', 'b.id')
                ->join([
                    'fenxiao_record',
                    'd'
                ], 'LEFT')
                ->on('d.orderid', '=', 'b.id')
                ->where('b.addtime', '>=', $params['start_date'])
                ->and_where('b.addtime', '<=', $params['end_date'])
                ->and_where('b.status', 'not in', [
                    0,
                    1,
                    3,
                    4
                ]);

            $total_list  = $model->execute()->as_array();
            $total_price = number_format(array_sum(array_column($total_list, 'amount')), 2);
            $total_num   = count($total_list);

            $brokerage_model = clone $model;
            $brokerage_list  = $brokerage_model->and_where('b.status', 'in', [
                2,
                6
            ])->execute()->as_array();
            $brokerage       = number_format(array_sum(array_column($brokerage_list, 'amount')), 2);
            $brokerage_num   = count($brokerage_list);

            $finish_brokerage_model = clone $model;
            $finish_brokerage_list  = $finish_brokerage_model->and_where('b.status', 'in', [5])->execute()->as_array();
            $finish_brokerage       = number_format(array_sum(array_column($finish_brokerage_list, 'amount')), 2);
            $finish_brokerage_num   = count($finish_brokerage_list);
        } else {
            $total_price          = 0;
            $total_num            = 0;
            $brokerage            = 0;
            $brokerage_num        = 0;
            $finish_brokerage     = 0;
            $finish_brokerage_num = 0;
        }


        return [
            'total_price'          => $total_price,
            'total_num'            => $total_num,
            'brokerage'            => $brokerage,
            'brokerage_num'        => $brokerage_num,
            'finish_brokerage'     => $finish_brokerage,
            'finish_brokerage_num' => $finish_brokerage_num
        ];
    }
}